package com.stone.business.jc.action;

/**
 * <pre>
 * 绩效考核统计
 * @author 
 * </pre>
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.stone.business.jc.pojo.TJxcp;
import com.stone.business.jc.service.JcCommonService;
import com.stone.para.IConstant;
import com.stone.sys.pojo.TGzltjPara;
import com.stone.utils.DateFormatTool;
import com.stone.utils.FileUtils;
import com.stone.utils.NumUtils;
import com.stone.utils.RandomString;
import com.stone.utils.Resources;
import com.stone.utils.StringUtils;

import frm.ssh.action.BaseListAction;
import frm.ssh.exception.AppException;

public class JxcprsdlAction extends BaseListAction {

	private static final long serialVersionUID = -4647594073681054487L;

	private JcCommonService myService;
	
	TJxcp entity;
	
	private String filenameen = ""; 
	
	// 下载用 返回的文件流
	protected InputStream returnStream;
	// 下载用的名字
	protected String downFileName;
	// 附件表ID
	private String id;
	
	/**
	 * 输入流对象
	 */
	private InputStream is;

	/**
	 * 创建Workbook对象
	 */
	private Workbook rwb;

	/**
	 * 进入详细页面
	 * 
	 * @return
	 * @throws AppException
	 */
	public String initDetail() throws AppException {
		return SUCCESS;
	}

	/**
	 * 进入详细页面
	 * 
	 * @return
	 * @throws AppException
	 */
	public String initTabDetail() throws AppException {

		return "TAB_SUCCESS";
	}

	/**
	 * 统计
	 * 
	 * @return
	 * @throws AppException
	 */
	@SuppressWarnings("unchecked")
	public String tongji() throws AppException {

		// 取得工作量统计参数
		List<TGzltjPara> list = myService.findListByHql("from TGzltjPara");

		TGzltjPara para = list.get(0);

		/*-
		 * 建一个二维数组 
		 * 行为人 列为项目  
		 * 列为要算分的项目数量
		 * 处理完后重新组合数组 把第0列为空的（没有用户的）都过滤掉。
		 */
		List<HashMap<String, Object>> tempList = myService.findListBySql("select max(id) as id from sys_user");

		// 以最大的id号作为行
		int row = Integer.parseInt(String.valueOf(tempList.get(0).get("id"))) + 1;
		
		// 开始结束时间
		int yjks = Integer.parseInt(para.getYjks().replace(":", ""));
		int yjjs = Integer.parseInt(para.getYjjs().replace(":", ""));
		/**
		 * 人员
		 */
		StringBuffer s = new StringBuffer();
	    s.append("select u.id as id,");
		s.append("    u.name as name,");
		s.append("    u.ygbh as ygbh,");
		s.append("    TO_CHAR(u.cssj,'yyyy-MM-dd') as cssj,");
		s.append("    (select dic5.item_Name  from sys_user u1,sys_dictionary dic5 where u1.id > 0 and u.id=u1.id and u1.JXKHGWJJXS = dic5.item_Id and  dic5.item_Type = 'JXKH_GWJJXS') as jxkhgwjjxs,");//by wangtao add20120425
		s.append("    (select dic6.item_Name  from sys_user u1,sys_dictionary dic6 where u1.id > 0 and u.id=u1.id and u1.JXKHJJZSXS = dic6.item_Id and  dic6.item_Type = 'JXKH_JJZSXS') as jxkhjjzsxs,");//by wangtao add20120425
		s.append("    u.jxkhflag as jxkhflag,");
		s.append("    u.zzmmflag as zzmmflag,");
		s.append("    dic1.item_Name as bzname,");
		s.append("    dic2.item_Name as gwname,");
		s.append("    dic3.item_Name as gjname,");
		s.append("    (select dic4.item_Name  from sys_user u1,sys_dictionary dic4 where u1.id > 0 and u.id=u1.id and u1.jxkhjz = dic4.item_Id and  dic4.item_Type = 'JXKH_JZ') as jzname");//by wangtao add20110415
		s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('3','4')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");
		s.append(" order by u.sortid");

		List<HashMap<String, Object>> tempListUser = myService.findListBySql(s.toString());
		
		int i = 0;

		// 为了排序生成的数组 
		int sort[]  = new int[row];
		Arrays.fill(sort, -1);
		
		// 定义统计量数组
		// 计算分数后的
		String str[][] = new String[tempListUser.size()][IConstant.GZLGJ_COL_COUNT];
		String str2[][] = new String[tempListUser.size()][32];//日均工作量
		
		// 先摆上人  sort 索引为用户ID，值为数组序号 从1开始 因为ID最小为1
		for (HashMap<String, Object> map : tempListUser) {
			sort[Integer.parseInt(map.get("id").toString())] = i;
			// 转化一把
			str[i][0] = map.get("name").toString();
			str[i][22] = map.get("jxkhflag").toString();
			str2[i][0] = map.get("name").toString();
			if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组安全员")){
				str[sort[Integer.parseInt(map.get("id").toString())]][15] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][15], 1, 1 , para.getPx11v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组技术员")){
				str[sort[Integer.parseInt(map.get("id").toString())]][14] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][14], 1, 1 , para.getPx21v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("班组主持")){
				str[sort[Integer.parseInt(map.get("id").toString())]][13] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][13], 1, 1 , para.getPx01v(), 1);
				
			}else if(map.get("jzname")!=null&&map.get("jzname").toString().equals("综合事务")){
				str[sort[Integer.parseInt(map.get("id").toString())]][16] = NumUtils.gzltj(str[sort[Integer.parseInt(map.get("id").toString())]][16], 1, 1 , para.getPx31v(), 1);
				
			}

	
			i++;
		}
		
		/**
		 * 工单工作日期
		 */
		
		conditions = "(state=6 or state=9) and TO_CHAR(rq,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(rq,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' ";
		
		tempList = myService.getListByDHql("czkssj as czkssj, czjssj as czjssj,ycclqk as gzfw, gs as gs, zbz as zbz,zbzname as zbzname,zby as zby,zbyname as zbyname,state as state,rq as rq", conditions, null, "", "TGzrwd s");

		for (HashMap<String, Object> map : tempList) {
			String state=(String)(map.get("state") == null ? "0":  map.get("state"));//0:9代表有票,5:6代表无票
			// 操作开始日期
			Date czkssjdr = DateFormatTool.formatToDate(DateFormatTool.format(((Date) map.get("czkssj")), false), false);
			// 实际日期
			Date rqdr =DateFormatTool.formatToDate(DateFormatTool.format(((Date) map.get("rq")), false), false);
			long days=1;
			if(state.equals("0")||state.equals("9")){
				days=1;
			}else{
				days= DateFormatTool.dayOfDiff(rqdr, czkssjdr)+1;
			}
			// 工时
			int gs = (Integer) (map.get("gs") == null ? 0 :  map.get("gs"));
			String gzfw=(String)map.get("gzfw");
			// 操作日期判断是否算夜间
			String czkssj = DateFormatTool.formate((Date) map.get("czkssj"), "HHmm");
			String czjssj = DateFormatTool.formate((Date) map.get("czjssj"), "HHmm");
			
			// 总负责人
			String[] zbz = ((String) (map.get("zbz") == null? "" : map.get("zbz"))).split(",");
			// 总负责人姓名
			String zbznames = ((String) (map.get("zbzname") == null? "" : map.get("zbzname")));
			// 成员
			String[] zby = ((String) (map.get("zby") == null? "" : map.get("zby"))).split(",");
			// 成员姓名
			String[] zbynames = ((String) (map.get("zbyname") == null? "" : map.get("zbyname"))).split(",");
			// 如果时间为空 就当 早上 9：30处理 按白天算 。一般不会出现 因为有操作人就有操作时间
			czkssj = "".equals(czkssj) ? "930" : czkssj;
			czjssj = "".equals(czjssj) ? "930" : czjssj;
			// 周末是否
			Date rq = ((Date) (map.get("rq") == null? "" : map.get("rq")));  
	        int n =DateFormatTool.dayOfWeek(rq, false);
			double zmxs=1;
			if (n==6||n==0) {
				zmxs=para.getXk21();
			}
			// 夜间倍数 乘 人/工作 除 实际多少人干了这个工作 
			double yjxs=1;
			if (Integer.parseInt(czkssj) >= yjks || Integer.parseInt(czkssj) <= yjjs || Integer.parseInt(czjssj) >= yjks || Integer.parseInt(czjssj) <= yjjs) {
			yjxs=para.getCz01();
			}
			
			// 工作负责人
			for (i = 0; i < zbz.length; i++) {
				
				if ("".equals(zbz[i])) {
					break;
				}
				
				// 20110329
				if (sort[Integer.parseInt(zbz[i])] == -1) {
					continue;
				}
				
			    double fzrxs=1;
				if(StringUtils.isNullOrBlankFull(gzfw)){
				fzrxs=para.getCz71();
				// 不管几个人都按照一次 
				//str[sort[Integer.parseInt(zbz[i])]][7] = NumUtils.gzltj(str[sort[Integer.parseInt(zbz[i])]][7], para.getCz00(), 1, yjxs*para.getCz12(), 1);
				
			      }else{//一种票
			    	  fzrxs=para.getCz61();
			    	// 不管几个人都按照一次 
					//	str[sort[Integer.parseInt(zbz[i])]][6] = NumUtils.gzltj(str[sort[Integer.parseInt(zbz[i])]][6], para.getCz00(), 1, yjxs*para.getCz02(), 1);
						
			      }
				// 实际工时
				str[sort[Integer.parseInt(zbz[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][1],gs,1, days*zmxs*fzrxs*yjxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zbz[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][3],gs,1, days*zmxs*fzrxs*yjxs, 1, 1);
				}
			
			// 成员
			for (i = 0; i < zby.length; i++) {
				
				if ("".equals(zby[i])) {
					break;
				}
				
				if (sort[Integer.parseInt(zby[i])] == -1) {
					continue;
				}
				// 如果成员包括负责人，只 算负责人
				if (zbznames.indexOf(zbynames[i]) != -1) {
					continue;
				}
				// 实际工时
				str[sort[Integer.parseInt(zby[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zby[i])]][1],gs, 1, days*zmxs*yjxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zby[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zby[i])]][3],gs, 1, days*zmxs*yjxs, 1, 1);
				if(StringUtils.isNullOrBlankFull(gzfw)){
				// 不管几个人都按照一次 
				//str[sort[Integer.parseInt(zby[i])]][7] = NumUtils.gzltj(str[sort[Integer.parseInt(zby[i])]][7], para.getCz00(), 1, yjxs*para.getCz12(), 1);
				}else{
					// 不管几个人都按照一次 
				//	str[sort[Integer.parseInt(zby[i])]][6] = NumUtils.gzltj(str[sort[Integer.parseInt(zby[i])]][6], para.getCz00(), 1, yjxs*para.getCz02(), 1);
					
				}
				}
		
		}
		
		/**
		 * 临时管理工作安排班组
		 */
		conditions = "TO_CHAR(startdate,'yyyy-MM-dd HH24:mi') >= '" 
			+ super.getSearchBean().getCon2() + " 00:00'"
			+ " AND TO_CHAR(startdate,'yyyy-MM-dd HH24:mi') <= '" 
			+ super.getSearchBean().getCon3() + " 23:59' and plantype=2";
		
		tempList = myService.getListByDHql("startdate as czkssj, enddate as czjssj,fzr as zbz,gs as gs", conditions, null, "", "TPlanWeekDetail s");

		for (HashMap<String, Object> map : tempList) {
			
			// 工时
			int gs = (Integer) (map.get("gs") == null ? 0 :  map.get("gs"));
			// 操作日期判断是否算夜间
			String czkssj = DateFormatTool.formate((Date) map.get("czkssj"), "HHmm");
			String czjssj = DateFormatTool.formate((Date) map.get("czjssj"), "HHmm");
			
			// 总负责人
			String[] zbz = ((String) (map.get("zbz") == null? "" : map.get("zbz"))).split(",");
			
			// 如果时间为空 就当 早上 9：30处理 按白天算 。一般不会出现 因为有操作人就有操作时间
			czkssj = "".equals(czkssj) ? "930" : czkssj;
			czjssj = "".equals(czjssj) ? "930" : czjssj;
			// 周末是否
			Date rq = ((Date) (map.get("czkssj") == null? "" : map.get("czkssj")));  
	        int n =DateFormatTool.dayOfWeek(rq, false);
			double zmxs=1;
			if (n==6||n==0) {
				zmxs=para.getXk21();
			}
			// 夜间倍数 乘 人/工作 除 实际多少人干了这个工作 
			double yjxs=1;
			if (Integer.parseInt(czkssj) >= yjks || Integer.parseInt(czkssj) <= yjjs || Integer.parseInt(czjssj) >= yjks || Integer.parseInt(czjssj) <= yjjs) {
			yjxs=para.getQx01();
			}
			
			// 工作负责人
			for (i = 0; i < zbz.length; i++) {
				
				if ("".equals(zbz[i])) {
					break;
				}
				
				// 20110329
				if (sort[Integer.parseInt(zbz[i])] == -1) {
					continue;
				}
				// 实际工时
				str[sort[Integer.parseInt(zbz[i])]][1] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][1],gs,1, zmxs, 1, 1);
				// 实际工时得分
				str[sort[Integer.parseInt(zbz[i])]][3] = NumUtils.gzltjCzcs(str[sort[Integer.parseInt(zbz[i])]][3],gs,1, zmxs, 1, 1);
				
				
				}
			
			
		}
		conditions = "TO_CHAR(khrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(khrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		
		// 工作质量得分
		tempList = myService.getListByDHql("sum(fs) as fs, userid as userid", conditions, null, "group by userid", "TGzzlkh");
		for (HashMap<String, Object> map : tempList) {
		
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][19] = String.valueOf((NumUtils.parseDouble(map.get("fs").toString())));
		}	
		
		// 专项考核
		tempList = myService.getListByDHql("sum(fs) as fs, userid as userid", conditions, null, "group by userid", "TZxkh");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][20] = String.valueOf((NumUtils.parseDouble(map.get("fs").toString())));
		}			
		// 班组考评
		List<HashMap<String, Object>> tempbzkpList = myService.getListByDHql("sum(fs) as fs, bzid as bzid", conditions, null, "group by bzid order by bzid", "TZxkhbz");
		
		// 考勤天数累计
		conditions = "TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		tempList = myService.getListByDHql("sum(qqts) as qqts, userid as userid", conditions, null, "group by userid", "TKq");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			
			str[sort[Integer.parseInt(map.get("userid").toString())]][21] = String.valueOf(NumUtils.parseInt((map.get("qqts") == null ? "" :map.get("qqts").toString())));
			//str[sort[Integer.parseInt(map.get("userid").toString())]][6] = map.get("qqyy") == null ? "" :map.get("qqyy").toString();
		}	
		// 考勤原因by wangtao add20120308
		conditions = "TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'"
		+ " AND TO_CHAR(kqrq,'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' ";
		tempList = myService.getListByDHql("qqyy as qqyy, userid as userid", conditions, null, "", "TKq");
		for (HashMap<String, Object> map : tempList) {
			
			if (sort[Integer.parseInt(map.get("userid").toString())] == -1) {
				continue;
			}
			//str[sort[Integer.parseInt(map.get("userid").toString())]][6] = str[sort[Integer.parseInt(map.get("userid").toString())]][6]+(map.get("qqyy") == null ? "" :map.get("qqyy").toString());
			}
		// 参与考核
		List<String[]> exp1 = new ArrayList();
		// 不参与考核
		List<String[]> exp2 = new ArrayList();
		// 日均工作量
		List<String[]> exp3 = new ArrayList();
		for (i=0; i<str.length;i++) {
				// 未参与工作量考核人员 就是不参与的和未值班>21天的
			if ("2".equals(str[i][22])) {
				exp2.add(str[i]);
			} else {
				exp1.add(str[i]);
				exp3.add(str2[i]);
			}
		}
		
		this.createExcel(tempListUser, exp1, exp2,exp3,tempbzkpList);
	
		if (this.entity == null) {
			entity = new TJxcp();
		}
		
		entity.setFilenamecn(Resources.getString("jxcprsdlname", "struts"));
		entity.setFilenameen(filenameen);
		entity.setTjsjks(DateFormatTool.formatToDate(super.getSearchBean().getCon2(), "yyyy-MM-dd"));
		entity.setTjsjjs(DateFormatTool.formatToDate(super.getSearchBean().getCon3(), "yyyy-MM-dd"));
		myService.save(entity);
		super.setInfo("统计失败");
		return SUCCESS;
	}
	
	/**
	 * 读取模版文件加入数据形成报表文件。
	 * 
	 * @param path 路径及文件名称
	 * @param list 数据列表 数组
	 */
	private void createNewExcel(String sheetname, List<HashMap<String,Object>> listUser, List<String[]> list1,List<String[]> list2,List<String[]> list3,List<HashMap<String,Object>> listBzkp) {
		try { 

			// 利用已经创建的Excel工作薄创建新的可写入的Excel工作薄
 			WritableWorkbook wwb = Workbook.createWorkbook(new File(sheetname), rwb);
			// 读取第一个sheet工作表
			WritableSheet ws1 = wwb.getSheet(1);

			/*// 员工基础信息表
			for (int i = 0; i < listUser.size(); i++) {
				
				HashMap<String,Object> map = listUser.get(i);
			
				// 姓名
				EditExcel(ws1, 0, i+1, (String)map.get("name"));
				// 员工号
				EditExcel(ws1, 7, i+1, (String)map.get("ygbh"));
				// 班组
				EditExcel(ws1, 1, i+1, (String)map.get("bzname"));
				// 奖金系数
				//EditExcel(ws1, 3, i+2, map.get("jxkhjjxs") == null ? "0" : map.get("jxkhjjxs").toString());
				EditExcel(ws1, 2, i+1, map.get("jxkhgwjjxs") == null ? 0 :NumUtils.parseDouble(map.get("jxkhgwjjxs").toString()) );
				// 岗位
				EditExcel(ws1, 3, i+1, (String)map.get("gwname"));
				// 岗级
				EditExcel(ws1, 4, i+1, (String)map.get("gjname"));
				// 兼职	
				EditExcel(ws1, 5, i+1, (String)map.get("jzname"));//by wangtao add 20110415
				String zzmm="";
				if("0".equals(map.get("zzmmflag"))){
					zzmm="党员";
				}else if("1".equals(map.get("zzmmflag"))){
					zzmm="团员";
				}else{
					zzmm="群众";
				}
				// 政治面貌
				EditExcel(ws1, 10, i+1, zzmm);
				EditExcel(ws1, 11, i+1, (map.get("cssj") == null? "" : (String)map.get("cssj")));
				// 奖金折算系数
				//EditExcel(ws1, 12, i+1, (map.get("jxkhjjzsxs") == null? "" : (String)map.get("jxkhjjzsxs")));
				EditExcel(ws1, 12, i+1, map.get("jxkhjjzsxs") == null ? 0 :NumUtils.parseDouble(map.get("jxkhjjzsxs").toString()) );
				

			}*/
			
			WritableSheet ws2 = wwb.getSheet(4);
			
			// 日期
			//EditExcel(ws2,5, 1, super.getSearchBean().getCon2());
			//EditExcel(ws2,6, 1, super.getSearchBean().getCon3());
			EditExcel(ws2,0, 0, ws2.getCell(0,0).getContents()+super.getSearchBean().getCon2() + " 00:00"+"<------>"+super.getSearchBean().getCon3() + " 23:59");
			
			// 参与考核的绩效得分表 sheet2 左侧
			for (int i = 0; i < list1.size(); i++) {
				for (int j = 0; j < IConstant.GZLGJ_COL_COUNT-6; j++) {
						if (j == 0 ) {
						EditExcel(ws2,j, i+2, list1.get(i)[j]);
					}else if(j!=0&&j!=18){//by wangtao update20110509
						EditExcel(ws2,j, i+2, NumUtils.parseDouble(NumUtils.roundHalfup(list1.get(i)[j],2)) );
						
					}

				}

			}
			int startcol = 23;
			
			/*// 不参考核的绩效得分表 sheet2 右侧
			for (int i = 0; i < list2.size(); i++) {
			
				// 姓名
				EditExcel(ws2, startcol, i+3, list2.get(i)[0]);
				// 	工作质量考核
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[3]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[19]) );
				
				// 专项考核
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[4]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[20]) );
				
				// 未值班的天数
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[5]);
				EditExcel(ws2, ++startcol, i+3, NumUtils.parseDouble(list2.get(i)[21]) );
				
				// 原因
				//EditExcel(ws2, ++startcol, i+3, list2.get(i)[6]);
				
				startcol = (startcol == 26 ? 23 : startcol);
				
			}
			//////日均工作量
        WritableSheet ws3 = wwb.getSheet(2);
				
			// 参与考核的绩效得分表 sheet2 左侧
			for (int i = 0; i < list3.size(); i++) {
				for (int j = 0; j < 32; j++) {
						if (j == 0 ) {
						EditExcel(ws3,j, i+2, list1.get(i)[j]);
					}else if(j!=0){//by wangtao update20110509
						EditExcel(ws3,j, i+2, NumUtils.parseDouble(NumUtils.roundHalfup(list3.get(i)[j],2)) );
						
					}

				}

			}*/
			// 读取第5个sheet工作表数据录入
			WritableSheet ws5 = wwb.getSheet(5);

			// 班组考评得分
			for (int i = 0; i < listBzkp.size(); i++) {
				
				HashMap<String,Object> map = listBzkp.get(i);
			
				//考评分数
				EditExcel(ws5, 5, i+4, map.get("fs") == null ? 0 :NumUtils.parseDouble(map.get("fs").toString()));
			
			}
	// 写入Excel对象
			wwb.write();
			wwb.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 通过模版创建新Excel。
	 * 
	 * @param sheetnameM 模版路径及文件名称
	 * @param sheetnameO 生成模版的名称包括路径
	 * @param list 数据列表 数据格式 数组
	 */
	public void createExcel(final List<HashMap<String,Object>> list, List<String[]> list1, List<String[]> list2,List<String[]> list3,final List<HashMap<String,Object>> listbzkp) {
		
		try {

			// 模板文件路径
			String fileFullPathM =  Resources.getString("modelpath", "struts") + Resources.getString("rsdljxcpmodel", "struts");
			// 生成文件名
			filenameen = DateFormatTool.format(new Date(), true).replace(" ", "").replaceAll("-", "").replaceAll(":", "") + RandomString.genRandomNum(8) + ".xls";
			// 创建生成文件路径
			FileUtils.createFolder(Resources.getString("jxcppath", "struts"));
			
			String fileFullPathO = Resources.getString("jxcppath", "struts") +  filenameen;

			// 读取模版文件形成输入流对象。
			if (this.is == null) {
				readExcel(fileFullPathM);
			}

			// 生成新表
			createNewExcel(fileFullPathO, list, list1, list2,list3,listbzkp);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeIs();
		}
	}
	
	/**
	 * 读取模版文件形成输入流对象。
	 * 
	 * @param sheetname 路径及文件名称
	 */
	private void readExcel(String sheetname) {
		try {

			// 创建Workbook对象 只读Workbook对象
			// 直接从本地文件创建Workbook
			// 从输入流创建Workbook
			is = new FileInputStream(sheetname);
			rwb = Workbook.getWorkbook(is);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void closeIs() {
		try {
			this.is.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void EditExcel(jxl.write.WritableSheet ws, int col, int row,
			   String value) throws Exception { // 字符串类型
			  jxl.write.Label labelC = new jxl.write.Label(col, row, value);
			  ws.addCell(labelC);
			 }
	public static void EditExcel(jxl.write.WritableSheet ws, int col, int row,
			   double value) throws Exception { // 添加 Number 对象 by wangtao add20110415
              jxl.write.Number labelN = new jxl.write.Number(col, row, value);
			  ws.addCell(labelN);
			 }

	/**
	 * 下载
	 * 
	 * @throws FileNotFoundException
	 */
	public String fileDownload() throws AppException, FileNotFoundException {

		TJxcp entity = (TJxcp) myService.findById(Long
				.parseLong(this.id), TJxcp.class.getName());

		returnStream = new java.io.FileInputStream( Resources.getString("jxcppath", "struts") + entity.getFilenameen());

		downFileName = entity.getFilenamecn();
		
		return "DOWNLOAD_SUCCESS";

	}
	
	/**
	 * 下载时需要显示的名字
	 */
	public String getDownloadFileName() throws Exception {

		downFileName = new String(downFileName.getBytes(), "ISO8859-1") + ".xls";

		return downFileName;

	}	
	
	/**
	 * 删除
	 * @return
	 * @throws AppException
	 */
	public String delete() throws AppException {
		
		myService.deleteJxcp(id);
		
		return "DEL-SUCCESS";
		
	}

	public void setMyService(JcCommonService myService) {
		this.service = myService;
		this.myService = myService;
	}

	public TJxcp getEntity() {
		return entity;
	}

	public void setEntity(TJxcp entity) {
		this.entity = entity;
	}

	public InputStream getReturnStream() {
		return returnStream;
	}

	public String getDownFileName() {
		return downFileName;
	}

	public void setId(String id) {
		this.id = id;
	}

	public void setReturnStream(InputStream returnStream) {
		this.returnStream = returnStream;
	}

	public void setDownFileName(String downFileName) {
		this.downFileName = downFileName;
	}
	
	/**
	 * 设置查询条件
	 */
	public HashMap<String, Object> setSearchCondition() {
		return null;
	}
	
	// 员工信息
	private List<HashMap<String,Object>> jxcpUserList;
	
	// 考勤情况
	private List<HashMap<String,Object>> jxcpKqList;
	
	// 工作质量考核记录
	private List<HashMap<String,Object>> jxcpGzzlkhList;
	
	// 专项考核
	private List<HashMap<String,Object>> jxcpZxkhList;

	String conditions = "";
	String conditions1 = "";
	/**
	 * 导出前信息查看员工信息
	 */
	public String getUserList() {
/*		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
		
*/		
		/**
		 * 人员
		 */
		StringBuffer s = new StringBuffer();
	    s.append("select u.id as id,");
		s.append("    u.name as name,");
		s.append("    u.ygbh as ygbh,");
		s.append("    u.jxkhjjxs as jxkhjjxs,");
		s.append("    u.jxkhflag as jxkhflag,");

		// 人员信息未参加工作量考核原因
		s.append("    case when u.jxkhflag = '4' then u.bkhyy");
		s.append("    else (case when (select qqts  from tab_kq kq where kq.userid = u.id");
		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59') > 21 then '未值班天数大于21天' else '' end )");
		s.append("    end as bkhyy,");
		
//		s.append("    (select qqyy from tab_kq kq where kq.userid = u.id");
//		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
//		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59' group by qqyy) as qqyy,");	
//		s.append("    (select qqts from tab_kq kq where kq.userid = u.id");
//		s.append("           and TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') >= '" + super.getSearchBean().getCon2() + " 00:00'");
//		s.append("           AND TO_CHAR(kqrq, 'yyyy-MM-dd HH24:mi') <= '" + super.getSearchBean().getCon3() + " 23:59') as qqts,");		
		s.append("    dic1.item_Name as bzname,");
		s.append("    dic2.item_Name as gwname,");
		s.append("    dic3.item_Name as gjname,");
		s.append("    (select dic4.item_Name  from sys_user u1,sys_dictionary dic4 where u1.id > 0 and u.id=u1.id and u1.jxkhjz = dic4.item_Id and dic4.item_Type = 'JXKH_JZ') as jzname");//by wangtao add20110415
		s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('3','4')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");
		/*s.append("   and u.jxkhjz = dic4.item_Id");//by wangtao add20110415
		s.append("   and dic4.item_Type = 'JXKH_JZ'");*/
		/*s.append("  from sys_user u,");
		s.append("    sys_dictionary dic1,");
		s.append("    sys_dictionary dic2,");
		s.append("    sys_dictionary dic3");
		s.append(" where u.id > 0");
		s.append("   and u.state = '1'");
		s.append("   and u.jxkhflag in ('1', '2')");
		s.append("   and u.jxkhbz = dic1.item_Id");
		s.append("   and dic1.item_Type = 'JXKH_BZ'");
		s.append("   and u.jxkhgw = dic2.item_Id");
		s.append("   and dic2.item_Type = 'JXKH_GW'");
		s.append("   and u.jxkhgj = dic3.item_Id");
		s.append("   and dic3.item_Type = 'JXKH_GJ'");*/
		s.append(" order by u.sortid");

		jxcpUserList = myService.findListBySql(s.toString());
		
		
		
		
		
/*		
		
		jxcpUserList = new ArrayList<HashMap<String,Object>>();
		
		// 参与绩效但是不考核的
		//List<HashMap<String,Object>> tempList = myService.getListByDHql("u.name as username, '0' as khflag, '0' as khyy, k.khrq as khrq", "u.id = k.userid and u.jxkhflag = '2'" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		List<HashMap<String,Object>> tempList = myService.getListByDHql("u.name as username, '0' as khflag, bkhyy as khyy", "u.jxkhflag = '2'", null, "order by u.sortid", "TUser u");
		jxcpUserList.addAll(tempList);
		
		// 参与绩效但是缺勤21天以上的
		tempList = myService.getListByDHql("u.name as username, '0' as khflag, k.qqyy || '缺勤超过21天' as khyy, k.kqrq as kqrq", "u.id = k.userid and u.jxkhflag = '1' and k.qqts >= 21" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		jxcpUserList.addAll(tempList);
		
		// 参与绩效并且缺勤21天以下的
		tempList = myService.getListByDHql("u.name as username, '1' as khflag, '' as khyy, k.kqrq as kqrq", "u.id = k.userid and u.jxkhflag = '1' and k.qqts < 21" + conditions, null, "order by u.sortid", "TKq k, TUser u");
		jxcpUserList.addAll(tempList);
*/
		
		return "JXCP_USER_SUCCESS";
	}
	
	/**
	 * 导出前信息查看考勤情况
	 */
	public String getKqList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.kqrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
	
		this.jxcpKqList = myService.getListByDHql("k.kqrq as kqrq, k.qqyy as qqyy, k.qqts as qqts, u.name as username", "u.id = k.userid and u.jxkhflag = '3' " + conditions, null, "order by u.sortid", "TKq k, TUser u");
		return "JXCP_KQ_SUCCESS";
	}
	
	/**
	 * 导出前信息查看工作质量考核记录
	 */
	public String getGzzlkhList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}
		
		this.jxcpGzzlkhList = myService.getListByDHql("k.fs as fs, k.khxzremark as khxzremark, k.khrq as khrq, u.name as username", "u.id = k.userid and u.jxkhflag = '3' " + conditions, null, "order by u.sortid", "TGzzlkh k, TUser u");
		return "JXCP_GZZLKH_SUCCESS";
	}
	
	/**
	 * 导出前信息查看专项考核
	 */
	public String getZxkhList() {
		
		conditions = "";
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon2())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') >= '" + super.getSearchBean().getCon2() + "'";
		}
		
		if (!StringUtils.isNullOrBlankFull(super.getSearchBean().getCon3())) {
			conditions += " AND TO_CHAR(k.khrq,'yyyy-MM-dd') <= '" + super.getSearchBean().getCon3() + "'";
		}

		this.jxcpZxkhList = myService.getListByDHql("k.fsyy as fsyy, k.fs as fs, k.khrq as khrq, u.name as username", "u.id = k.userid and u.jxkhflag = '3' " + conditions, null, "order by u.sortid", "TZxkh k, TUser u");
		return "JXCP_ZXKH_SUCCESS";
	}


	public List<HashMap<String, Object>> getJxcpUserList() {
		return jxcpUserList;
	}


	public List<HashMap<String, Object>> getJxcpKqList() {
		return jxcpKqList;
	}


	public List<HashMap<String, Object>> getJxcpGzzlkhList() {
		return jxcpGzzlkhList;
	}


	public List<HashMap<String, Object>> getJxcpZxkhList() {
		return jxcpZxkhList;
	}

}